package stores;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import workspace.JdbcTools;
import workspace.Workspace;
import beans.Foglalas;

public class FoglalasStoreImpl implements FoglalasStore {

	@Override
	public List<Foglalas> loadFoglalasFromDate(Workspace workspace,
			Calendar fromDate) throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		Foglalas foglalas = null;
		List<Foglalas> foglalasok = new LinkedList<Foglalas>();

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, FOGLALASKOD, ELRENDEZES, MEGJEGYZES, FOGLALOID, TEREMID, TIPUSID, FROMDATE, TODATE, FOGLALASIAR, FOGLALASNEV FROM FOGLALAS WHERE FROMDATE > ?");
			int i = 1;
			JdbcTools.setTimestamp(ps, i++, fromDate.getTime());
			rs = ps.executeQuery();
			while (rs.next()) {
				foglalas = buildFoglalas(rs);
				foglalasok.add(foglalas);
			}
			return foglalasok;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}

	}

	@Override
	public Foglalas loadFoglalasById(Workspace workspace, long foglalasId)
			throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Foglalas foglalas = null;

		try {
			ps = workspace
					.getPreparedStatement("SELECT ID, ROWVERSION, FOGLALASKOD, ELRENDEZES, MEGJEGYZES, FOGLALOID, TEREMID, TIPUSID, FROMDATE, TODATE, FOGLALASIAR, FOGLALASNEV FROM FOGLALAS WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, foglalasId);
			rs = ps.executeQuery();
			if (rs.next()) {
				foglalas = buildFoglalas(rs);
			}
			return foglalas;

		} finally {
			if (ps != null) {
				ps.close();
			}
			if (rs != null) {
				rs.close();
			}
		}
	}

	@Override
	public Foglalas insertFoglalas(Workspace workspace, Foglalas newFoglalas)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			StringBuilder sqlBuilder = new StringBuilder();
			// sqlBuilder.append("declare @a datetime");
			// sqlBuilder.append("declare @b datetime");
			//
			// sqlBuilder.append("set @a = ?");
			// sqlBuilder.append("set @b = ?");

			sqlBuilder
					.append("exec dbo.insertfoglalas @writer = ?, @elrendezes = ?, @megjegyzes = ?, @foglaloId = ?, @teremId = ?, @tipusId = ?, @fromDate = ?,  @toDate = ?, @foglalasiAr = ?, @foglalasNev = ?;");

			ps = workspace.getPreparedStatement(sqlBuilder.toString());
			ps.setEscapeProcessing(true);
			ps.setQueryTimeout(10000);
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			ps.setString(i++, newFoglalas.getElrendezes());
			ps.setString(i++, newFoglalas.getMegjegyzes());
			ps.setLong(i++, newFoglalas.getFoglalo().getId());
			ps.setLong(i++, newFoglalas.getTerem().getId());
			ps.setLong(i++, newFoglalas.getTipus().getId());
			JdbcTools
					.setTimestamp(ps, i++, newFoglalas.getFromDate().getTime());
			JdbcTools.setTimestamp(ps, i++, newFoglalas.getToDate().getTime());
			ps.setLong(i++, newFoglalas.getFoglalasiAr());
			ps.setString(i++, newFoglalas.getFoglalasNev());
			boolean sorValtNum = ps.execute();
			if (!sorValtNum) {
				newFoglalas.setId(getGeneratedKey(workspace));
				newFoglalas.setFoglalasKod(getGeneratedFoglalasKod(workspace,
						newFoglalas.getId()));
				newFoglalas.setRowVersion(1);
				workspace.commit();
				return newFoglalas;
			} else {
				return null;
			}
		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}

	}

	@Override
	public boolean updateFoglalas(Workspace workspace, Foglalas updatedFoglalas)
			throws SQLException {
		PreparedStatement ps = null;
		StringBuilder sql = new StringBuilder();

		workspace.startSession();

		sql.append("UPDATE FOGLALAS SET WRITER = ?");
		if (updatedFoglalas.getElrendezes() != null) {
			sql.append(", ELRENDEZES = ?");
		}
		if (updatedFoglalas.getMegjegyzes() != null) {
			sql.append(", MEGJEGYZES = ?");
		}
		if (updatedFoglalas.getFoglalo() != null) {
			sql.append(", FOGLALOID = ?");
		}
		if (updatedFoglalas.getTerem() != null) {
			sql.append(", TEREMID = ?");
		}
		if (updatedFoglalas.getTipus() != null) {
			sql.append(", TIPUSID = ?");
		}
		if (updatedFoglalas.getFromDate() != null) {
			sql.append(", FROMDATE = ?");
		}
		if (updatedFoglalas.getToDate() != null) {
			sql.append(", TODATE = ?");
		}
		if (updatedFoglalas.getFoglalasiAr() != null) {
			sql.append(", FOGLALASIAR = ?");
		}
		if (updatedFoglalas.getFoglalasNev() != null) {
			sql.append(", FOGLALASNEV = ?");
		}
		sql.append(" WHERE ID = ? AND ROWVERSION = ?");

		try {
			ps = workspace.getPreparedStatement(sql.toString());
			int i = 1;
			ps.setLong(i++, workspace.getAktualisOperator().getId());
			if (updatedFoglalas.getElrendezes() != null) {
				ps.setString(i++, updatedFoglalas.getElrendezes());
			}
			if (updatedFoglalas.getMegjegyzes() != null) {
				ps.setString(i++, updatedFoglalas.getMegjegyzes());
			}
			if (updatedFoglalas.getFoglalo() != null) {
				ps.setLong(i++, updatedFoglalas.getFoglalo().getId());
			}
			if (updatedFoglalas.getTerem() != null) {
				ps.setLong(i++, updatedFoglalas.getTerem().getId());
			}
			if (updatedFoglalas.getTipus() != null) {
				ps.setLong(i++, updatedFoglalas.getTipus().getId());
			}
			if (updatedFoglalas.getFromDate() != null) {
				JdbcTools.setTimestamp(ps, i++, updatedFoglalas.getFromDate()
						.getTime());
			}
			if (updatedFoglalas.getToDate() != null) {
				JdbcTools.setTimestamp(ps, i++, updatedFoglalas.getToDate()
						.getTime());
			}
			if (updatedFoglalas.getFoglalasiAr() != null) {
				ps.setLong(i++, updatedFoglalas.getFoglalasiAr());
			}
			if (updatedFoglalas.getFoglalasNev() != null) {
				ps.setString(i++, updatedFoglalas.getFoglalasNev());
			}
			ps.setLong(i++, updatedFoglalas.getId());
			ps.setLong(i++, updatedFoglalas.getRowVersion());
			int valtSorNum = ps.executeUpdate();
			if (valtSorNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	@Override
	public boolean deleteFoglalas(Workspace workspace, long foglalasId)
			throws SQLException {
		PreparedStatement ps = null;

		workspace.startSession();

		try {
			ps = workspace.getPreparedStatement("DELETE FOGLALAS WHERE ID = ?");
			int i = 1;
			ps.setLong(i++, foglalasId);
			int sorValtNum = ps.executeUpdate();
			if (sorValtNum == 1) {
				workspace.commit();
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			workspace.rollback();
			throw new SQLException(e);
		} finally {
			if (ps != null) {
				ps.close();
			}
		}
	}

	private Foglalas buildFoglalas(ResultSet rs) throws SQLException {
		Foglalas newFoglalas = new Foglalas();
		int i = 1;
		newFoglalas.setId(rs.getLong(i++));
		newFoglalas.setRowVersion(rs.getLong(i++));
		newFoglalas.setFoglalasKod(rs.getString(i++));
		newFoglalas.setElrendezes(rs.getString(i++));
		newFoglalas.setMegjegyzes(rs.getString(i++));
		newFoglalas.setFoglaloId(rs.getLong(i++));
		newFoglalas.setTeremId(rs.getLong(i++));
		newFoglalas.setTipusId(rs.getLong(i++));
		// Konverzio DATE->CALENDAR
		Date tempDate = JdbcTools.getTimestamp(rs, i++);
		Calendar fromCalendar = Calendar.getInstance();
		fromCalendar.setTime(tempDate);
		newFoglalas.setFromDate(fromCalendar);
		// Konverzio DATE->CALENDAR
		tempDate = JdbcTools.getTimestamp(rs, i++);
		Calendar toCalendar = Calendar.getInstance();
		toCalendar.setTime(tempDate);
		newFoglalas.setToDate(toCalendar);
		newFoglalas.setFoglalasiAr(rs.getLong(i++));
		newFoglalas.setFoglalasNev(rs.getString(i++));
		return newFoglalas;
	}

	private long getGeneratedKey(Workspace workspace) throws SQLException {
		PreparedStatement ps = workspace
				.getPreparedStatement("SELECT IDENT_CURRENT('FOGLALAS')");
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			return rs.getLong(1);
		} else
			return -1L;
	}

	private String getGeneratedFoglalasKod(Workspace workspace, long id)
			throws SQLException {
		PreparedStatement ps = workspace
				.getPreparedStatement("SELECT FOGLALASKOD FROM FOGLALAS WHERE ID=?");
		ps.setLong(1, id);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			return rs.getString(1);
		} else
			return null;
	}

}
